/**   
* @Title: ExportHelper.java 
* @Package com.unism.util 
* @Description: TODO(用一句话描述该文件做什么) 
* @author HS 
* @date 2016年5月11日 上午9:27:50 
* @version V1.0   
*/
package com.unism.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;






/**
 *@Title:ExportHelper.java
 *@Package com.unism.util
 *@Description:TODO
 *@author HS
 *@date 2016年5月11日 上午9:27:50
 *@version V1.0
 */
/**
 * @ClassName:ExportHelper
 * @Description:TODO
 * @author HS
 * @date 2016年5月11日 上午9:27:50
 *
 */
public class ExportHelper {
	
	
	/**
	 * 
	 * @MethodName:getPushbackInputStream
	 * @Description:TODO
	 * @author HS
	 * @date 2016年5月19日 下午12:37:18
	 *
	 * @param in
	 * @return
	 */
	public static  PushbackInputStream getPushbackInputStream(InputStream in){ 
		PushbackInputStream pbin = new PushbackInputStream(in);
		if (!in.markSupported()) {
			pbin = new PushbackInputStream(in,8);
		}
		return pbin;
	}
	/**
	 * 
	 * @MethodName:IsXLSX
	 * @Description:excel版本
	 * @author HS
	 * @date 2016年5月19日 下午1:02:15
	 *
	 * @param pbin
	 * @return
	 * @throws IOException 
	 */
	public static Boolean IsXLSX(PushbackInputStream pbin) throws IOException{
	    if (POIFSFileSystem.hasPOIFSHeader(pbin)) {
	    	return false;
        }
        else if (POIXMLDocument.hasOOXMLHeader(pbin)) {
        	return true;
        }
	    return false;
	}
	/**
	 * 
	 * @MethodName:exportListFromExcel
	 * @Description:读取excel
	 * @author HS
	 * @date 2016年5月19日 下午12:29:27
	 *
	 * @param in
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 */
//	public static List<List<String>> exportListFromExcel(PushbackInputStream pbin,Integer sheetNum) throws IOException{
//		List<List<String>> listInfo=new ArrayList<List<String>>();
//        if (POIFSFileSystem.hasPOIFSHeader(pbin)) {
//        	listInfo= readXLS(pbin, sheetNum);
//        }
//        else if (POIXMLDocument.hasOOXMLHeader(pbin)) {
//        	listInfo= readXLSX(pbin,sheetNum);
//        }
//		return listInfo;
//	}
	/**
	 * 
	 * @MethodName:getWorkbook
	 * @Description:TODO
	 * @author HS
	 * @date 2016年5月19日 下午1:15:14
	 *
	 * @param input
	 * @param isXLXS
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbook(InputStream input,Boolean isXLXS) throws IOException{
		Workbook workbook=null;
		if(isXLXS){
		  workbook = new XSSFWorkbook (input);
		}
		else{
			 workbook = new HSSFWorkbook(input);
		}
		return workbook;
	}
	/**
	 * 
	 * @MethodName:getWorkbookByInputStream
	 * @Description:TODO
	 * @author HS
	 * @date 2016年5月19日 下午1:18:20
	 *
	 * @param in
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbookByInputStream(InputStream in) throws IOException{
		PushbackInputStream pin=getPushbackInputStream(in);
		Boolean isXLSXS=IsXLSX(pin);
		return getWorkbook(pin,isXLSXS);
	}
	
	/**
	 * 
	 * @MethodName:exportListFromExcel
	 * @Description:TODO
	 * @author HS
	 * @date 2016年5月11日 上午9:48:49
	 *
	 * @param input
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 */
	public static List<List<String>> readXLS(Workbook workbook,Integer sheetNum) throws IOException{
		  Sheet sheet = workbook.getSheetAt(sheetNum);

	        // 解析公式结果
	        FormulaEvaluator evaluator = workbook.getCreationHelper()
	                .createFormulaEvaluator();

	        List<List<String>> list = new ArrayList<List<String>>();

	        int minRowIx = sheet.getFirstRowNum();
	        int maxRowIx = sheet.getLastRowNum();
	        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++)
	        {
	            Row row = sheet.getRow(rowIx);
	            //StringBuilder sb = new StringBuilder();

	            short minColIx = row.getFirstCellNum();
	            short maxColIx = row.getLastCellNum();
	            List<String> collList=new ArrayList<String>();
	            for (short colIx = minColIx; colIx <= maxColIx; colIx++)
	            {
	                Cell cell = row.getCell(new Integer(colIx));
	                CellValue cellValue = evaluator.evaluate(cell);
	                if (cellValue == null){
	                	collList.add("");
	                    continue;
	                }
	                Object result = null;
	                switch (cell.getCellType())
	                {
	                   case Cell.CELL_TYPE_BOOLEAN:
	                       result = cell.getBooleanCellValue();
	                    break;
	                    case HSSFCell.CELL_TYPE_NUMERIC:
	                        if (HSSFDateUtil.isCellDateFormatted(cell))
	                        {   
	                            // 处理日期格式、时间格式
	                            SimpleDateFormat sdf = null;
	                            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm"))
	                            {
	                                sdf = new SimpleDateFormat("HH:mm");
	                            }
	                            else if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm:ss"))
	                            {
	                                sdf = new SimpleDateFormat("HH:mm:ss");
	                            }
	                            else
	                            {
	                                sdf = new SimpleDateFormat("yyyy-MM-dd");
	                            }
	                            Date date = cell.getDateCellValue();
	                            result = sdf.format(date);
	                        }
	                        else if (cell.getCellStyle().getDataFormat() == 58)
	                        {
	                            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
	                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	                            double value = cell.getNumericCellValue();
	                            Date date = org.apache.poi.ss.usermodel.DateUtil
	                                    .getJavaDate(value);
	                            result = sdf.format(date);
	                        }
	                        else
	                        {
	                            double value = cell.getNumericCellValue();
	                            CellStyle style = cell.getCellStyle();
	                            DecimalFormat format = new DecimalFormat();
	                            String temp = style.getDataFormatString();
	                            // 单元格设置成常规
	                            if (temp.equals("General"))
	                            {
	                               // format.applyPattern("#");
	                            }
	                            result = format.format(value);
	                        }
	                        break;
	                    case HSSFCell.CELL_TYPE_STRING:
	                        result = cell.getRichStringCellValue().toString();
	                        break;
	                    case Cell.CELL_TYPE_FORMULA:
	                        break;
	                    case Cell.CELL_TYPE_BLANK:
	                        break;
	                    case Cell.CELL_TYPE_ERROR:
	                        break;
	                    default:
	                        break;
	                }
	                collList.add(result.toString());
	            }
	            list.add(collList);
	        }
	        return list;
	}
	
	public static List<List<String>> readXLSX(Workbook workbook,Integer sheetNum) throws IOException{
	  Sheet sheet = workbook.getSheetAt(sheetNum);

        // 解析公式结果
        FormulaEvaluator evaluator = workbook.getCreationHelper()
                .createFormulaEvaluator();

        List<List<String>> list = new ArrayList<List<String>>();

        int minRowIx = sheet.getFirstRowNum();
        int maxRowIx = sheet.getLastRowNum();
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++)
        {
            Row row = sheet.getRow(rowIx);
            //StringBuilder sb = new StringBuilder();

            short minColIx = row.getFirstCellNum();
            short maxColIx = row.getLastCellNum();
            List<String> collList=new ArrayList<String>();
            for (short colIx = minColIx; colIx <= maxColIx; colIx++)
            {
                Cell cell = row.getCell(new Integer(colIx));
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                	collList.add("");
                    continue;
                }
                Object result = null;
                switch (cell.getCellType())
                {
                   case Cell.CELL_TYPE_BOOLEAN:
                       result = cell.getBooleanCellValue();
                    break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell))
                        {   
                            // 处理日期格式、时间格式
                            SimpleDateFormat sdf = null;
                            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm"))
                            {
                                sdf = new SimpleDateFormat("HH:mm");
                            }
                            else if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm:ss"))
                            {
                                sdf = new SimpleDateFormat("HH:mm:ss");
                            }
                            else
                            {
                                sdf = new SimpleDateFormat("yyyy-MM-dd");
                            }
                            Date date = cell.getDateCellValue();
                            result = sdf.format(date);
                        }
                        else if (cell.getCellStyle().getDataFormat() == 58)
                        {
                            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            double value = cell.getNumericCellValue();
                            Date date = org.apache.poi.ss.usermodel.DateUtil
                                    .getJavaDate(value);
                            result = sdf.format(date);
                        }
                        else
                        {
                            double value = cell.getNumericCellValue();
                            CellStyle style = cell.getCellStyle();
                            DecimalFormat format = new DecimalFormat();
                            String temp = style.getDataFormatString();
                            // 单元格设置成常规
                            if (temp.equals("General"))
                            {
                               // format.applyPattern("#");
                            }
                            result = format.format(value);
                        }
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        result = cell.getRichStringCellValue().toString();
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    default:
                        break;
                }
                collList.add(result.toString());
            }
            list.add(collList);
        }
        return list;
}

}
